package com.birdpiss.commons.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.PreparedStatementCreatorFactory;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.birdpiss.commons.dao.interfaces.AccountDao;
import com.birdpiss.commons.domain.Account;
import com.birdpiss.commons.domain.AccountType;

/**
 * The Class DefaultAccountDao.
 *
 * @author mark
 */
@Repository("accountDao")
public class DefaultAccountDao extends SimpleJdbcTemplate implements AccountDao {

	private static final Logger log = Logger.getLogger(AccountDao.class);
	protected static final String [] COLUMNS = { "Id", "RoutingNumber", "AccountNumber", "AccountTypeId" };
	protected AccountRowMapper accountRowMapper = new AccountRowMapper();
	private PreparedStatementCreatorFactory insertAccount;

	protected static final String SELECT_BY_ID =
		"SELECT " + StringUtils.arrayToCommaDelimitedString(COLUMNS) +
		" FROM account " +
		"WHERE Id = ?";

	protected static final String SELECT_BY_USER_ID =
		"SELECT " + StringUtils.arrayToCommaDelimitedString(COLUMNS) +
		" FROM account a " +
		"INNER JOIN account_user au ON a.Id = au.AccountId " +
		"WHERE au.UserId = ?";

	protected static final String INSERT_ACCOUNT =
		"INSERT INTO account " +
		"(" + COLUMNS[1] + ", " + COLUMNS[2] + ", " + COLUMNS[3] + ") " +
		"VALUES (?, ?, ?)";

	protected static final String SELECT_ACCOUNT_BY_ALL =
		"SELECT Id, RoutingNumber, AccountNumber, AccountTypeId " +
		"FROM account " +
		"WHERE RoutingNumber = ? AND AccountNumber = ? AND AccountTypeId = ?";

	/**
	 * Instantiates a new default account dao.
	 *
	 * @param dataSource the data source
	 */
	@Autowired
	public DefaultAccountDao(@Qualifier("birdpiss") DataSource dataSource) {
		super(dataSource);
		this.initializePreparedStatements();
	}

	/**
	 * Initialize prepared statements.
	 */
	private void initializePreparedStatements() {
		this.insertAccount =
			new PreparedStatementCreatorFactory(INSERT_ACCOUNT,
					                            new int[] { Types.VARCHAR, Types.VARCHAR, Types.INTEGER });

		this.insertAccount.setReturnGeneratedKeys(true);
	}

	/* (non-Javadoc)
	 * @see com.birdpiss.commons.dao.interfaces.AccountDao#getAccountById(java.lang.Integer)
	 */
	@Override
	public Account getAccountById(Integer accountId) {
		if (log.isDebugEnabled()) log.debug("Getting account id: " + accountId);
		List<Account> account = this.query(SELECT_BY_ID, this.accountRowMapper, accountId);
		return ((account != null) && (account.size() == 1)) ? account.get(0) : null;
	}

	/* (non-Javadoc)
	 * @see com.birdpiss.commons.dao.interfaces.AccountDao#getAccountByUserId(java.lang.Integer)
	 */
	@Override
	public Account getAccountByUserId(Integer userId) {
		if (log.isDebugEnabled()) log.debug("Getting account for user id: " + userId);
		List<Account> account = this.query(SELECT_BY_USER_ID, this.accountRowMapper, userId);
		return ((account != null) && (account.size() == 1)) ? account.get(0) : null;
	}

	/* (non-Javadoc)
	 * @see com.birdpiss.commons.dao.interfaces.AccountDao#insertAccount(com.birdpiss.commons.domain.Account)
	 */
	@Override
	public Account insertAccount(Account account) {
		if (log.isDebugEnabled()) log.debug("Inserting Account: " + account);

		KeyHolder keyHolder = new GeneratedKeyHolder();
		this.getJdbcOperations().update(
				this.insertAccount.newPreparedStatementCreator(new Object[] {
						account.getRoutingNumber(),
						account.getAccountNumber(),
						account.getAccountType().value() }),
					keyHolder);

		int primaryKey = keyHolder.getKey().intValue();
		if (log.isDebugEnabled()) log.debug("Inserted Account id: " + primaryKey);
		account.setId(primaryKey);

		return account;
	}

	/* (non-Javadoc)
	 * @see com.birdpiss.commons.dao.interfaces.AccountDao#getAccountCount(com.birdpiss.commons.domain.Account)
	 */
	public Account getAccount(Account account) {
		List<Account> accounts = this.query(SELECT_ACCOUNT_BY_ALL,
				                            this.accountRowMapper,
				                            account.getRoutingNumber(),
				                            account.getAccountNumber(),
				                            account.getAccountType().value());
		return ((accounts != null) && (accounts.size() == 1)) ? accounts.get(0) : null;
	}

	/**
	 * The class AccountRowMapper.
	 */
	protected class AccountRowMapper implements ParameterizedRowMapper<Account> {

		/* (non-Javadoc)
		 * @see org.springframework.jdbc.core.simple.ParameterizedRowMapper#mapRow(java.sql.ResultSet, int)
		 */
		@Override
		public Account mapRow(ResultSet rs, int rowNum) throws SQLException {

			return new Account(rs.getInt(COLUMNS[0]),
					           rs.getString(COLUMNS[1]),
					           rs.getString(COLUMNS[2]),
					           AccountType.getAccountType(rs.getInt(COLUMNS[3])));
		}
	}

}
